package com.tnstart.db
{
	import com.tnstart.vo.Area;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	
	import mx.collections.ArrayCollection;

	public class AreaDAO
	{
		private var _table:String = "area";
		
		private var _conn:SQLConnection;
		
		
		public function AreaDAO(conn:SQLConnection)
		{
			_conn = conn;
			createTable();
		}
		
		
		public function createTable():void
		{
			var createStmt:SQLStatement = new SQLStatement();
			createStmt.sqlConnection = _conn;
			var sql:String = "";
			sql += "CREATE TABLE IF NOT EXISTS area (";
			sql += "	areaid		INTEGER PRIMARY KEY,";
			sql += "	name	TEXT,";
			sql += "	type		INTEGER";
			sql += ")";
			createStmt.text = sql;
			
			try
			{
				createStmt.execute();
			}
			catch (error:SQLError)
			{
				trace("Error creating table");
				trace("CREATE TABLE error:", error);
				trace("error.message:", error.message);
				trace("error.details:", error.details);
				return;
			}
		}
		
		public function list():ArrayCollection{
			var query:String = "";
			
			query = "SELECT * " + 
				"FROM  area " +
				"ORDER BY type, areaid";
			
			var sqlSelect:SQLStatement = new SQLStatement();
			sqlSelect.sqlConnection = _conn;
			
			sqlSelect.text = query;
			sqlSelect.execute();
			var result:SQLResult =  sqlSelect.getResult();
			
			
			var ac:ArrayCollection = new ArrayCollection();
			if (result.data != null){
				for (var i:int = 0; i < result.data.length; i++){
					var area:Area = convertPlainObjectToItem(result.data[i]);
					ac.addItem(area);
				}
			}
			return ac;
			
		}
		
		public function get(id:int):Area{
			var query:String = "";
			
			query = "SELECT * " + 
					"FROM  	area " + 
					"WHERE 	areaid = :areaid";
			
			var sqlSelect:SQLStatement = new SQLStatement();
			sqlSelect.sqlConnection = _conn;
			sqlSelect.parameters[":areaid"] = id;
			
			sqlSelect.text = query;
			sqlSelect.execute();
			var result:SQLResult =  sqlSelect.getResult();
			
			var area:Area = convertPlainObjectToItem(result.data[0]);
			
			return area;
		}
		
		public function getItemByName(name:String):Area{
			var query:String = "";
			
			query = "SELECT * " + 
				"FROM  area " + 
				"WHERE name = :name";
			
			var sqlSelect:SQLStatement = new SQLStatement();
			sqlSelect.sqlConnection = _conn;
			sqlSelect.parameters[":name"] = name;
			
			sqlSelect.text = query;
			sqlSelect.execute();
			var result:SQLResult =  sqlSelect.getResult();
			
			var area:Area = new Area();
			if (result.data && result.data.length > 0){
				area = convertPlainObjectToItem(result.data[0]);
			}
			return area;
		}
		
		private function convertPlainObjectToItem(obj:Object):Area
		{
			var area:Area = new Area();
			area.areaid = obj.areaid;
			area.name = obj.name;
			area.type = obj.type;
			return area;
		}		
		
		public function save(area:Area):void {
			if(area.areaid != 0){
				update(area);
			}
			else{
				insert(area);
			}
		}
		
		public function insert(area:Area):void {
			var query:String = "INSERT INTO area (" + 
				"name," + 
				"type)" + 
				"VALUES ( " + 
				":name," + 
				":type)";
			
			var sqlInsert:SQLStatement = new SQLStatement();
			sqlInsert.sqlConnection = _conn;
			//sqlInsert.addEventListener( SQLEvent.RESULT, onSQLSave );
			//sqlInsert.addEventListener( SQLErrorEvent.ERROR, onSQLError );				
			
			sqlInsert.text = query;
			sqlInsert.parameters[":name"] = area.name;
			sqlInsert.parameters[":type"] = area.type; 

			
			
			sqlInsert.execute();	
		}
		
		public function destroy(area:Area):void {
			var query:String = "DELETE FROM area " + 
				"WHERE areaid = " + area.areaid + " ";
			
			var sqlInsert:SQLStatement = new SQLStatement();
			sqlInsert.sqlConnection = _conn;
			//sqlInsert.addEventListener( SQLEvent.RESULT, onSQLSave );
			//sqlInsert.addEventListener( SQLErrorEvent.ERROR, onSQLError );				
			
			sqlInsert.text = query;
			
			sqlInsert.execute();	
		}
		
		public function delByid(areaid:int):void {
			var query:String = "DELETE FROM area " + 
				"WHERE areaid = " + areaid + " ";
			
			var sqlInsert:SQLStatement = new SQLStatement();
			sqlInsert.sqlConnection = _conn;

			sqlInsert.text = query;
			
			sqlInsert.execute();	
		}
		
		
		public function update(area:Area):void {
			var query:String = "UPDATE area " + 
				"set " + 
				"name ='" + area.name +  "', " + 
				"type ='" + area.type +  "' " +
				"WHERE areaid = " + area.areaid + " ";
			
			var sqlInsert:SQLStatement = new SQLStatement();
			sqlInsert.sqlConnection = _conn;
			//sqlInsert.addEventListener( SQLEvent.RESULT, onSQLSave );
			//sqlInsert.addEventListener( SQLErrorEvent.ERROR, onSQLError );				
			
			sqlInsert.text = query;
			
			sqlInsert.execute();	
		}
		
		public function getLastRecordID():int{
			var query:String = "";
			
			query = "SELECT last_insert_rowid() as id";
			
			var sqlSelect:SQLStatement = new SQLStatement();
			sqlSelect.sqlConnection = _conn;
			
			sqlSelect.text = query;
			sqlSelect.execute();
			var result:SQLResult =  sqlSelect.getResult();
			
			var lastID:int = result.data[0].id;
			
			return lastID;
		}
		
		public function getMaxItemId():int{
			var query:String = "";
			
			query = "SELECT areaid from area order by areaid desc";
			
			var sqlSelect:SQLStatement = new SQLStatement();
			sqlSelect.sqlConnection = _conn;
			
			sqlSelect.text = query;
			sqlSelect.execute();
			var result:SQLResult =  sqlSelect.getResult();
			
			var lastID:int = result.data[0].areaid;
			
			return lastID;
		}
		
	}
}